/********************************************************************** 
 * FILE		: ExeclUtilTools.java					
 *			
 * AUTHOR   : kloyf	
 * 
 * DATE		: 2015-3-25
 * 									
 * REASON	: CREATE  
 *
 ***********************************************************************/
package com.common.util.file.execl;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**********************************************************************
 * @author kloyf
 * 
 * @version 1.0
 * 
 ***********************************************************************/
public class ExeclUtilTools extends AbstractExeclUtil {
	/**
	 * read the Excel file
	 * 
	 * @param path
	 *            the path of the Excel file
	 * @return
	 * @throws IOException
	 */
	public static List<?> readExcel(String path) {
		if (path == null || EMPTY.equals(path)) {
			return null;
		} else {
			// String postfix = Util.getPostfix(path);
			if (!EMPTY.equals(path)) {
				if (OFFICE_EXCEL_2003_POSTFIX.equals(path)) {
					try {
						return readXls(path);
					} catch (IOException e) {
						e.printStackTrace();
					}
				} else if (OFFICE_EXCEL_2010_POSTFIX.equals(path)) {
					try {
						return readXlsx(path);
					} catch (IOException e) {
						e.printStackTrace();
					}
				}
			} else {
				System.out.println(path + NOT_EXCEL_FILE);
			}
		}
		return null;
	}

	/**
	 * Read the Excel 2010
	 * 
	 * @param path
	 *            the path of the excel file
	 * @return
	 * @throws IOException
	 */
	public static List<?> readXlsx(String path) throws IOException {
		System.out.println(PROCESSING + path);
		InputStream is = new FileInputStream(path);
		XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
		Object student = null;
		List<?> list = new ArrayList<Object>();
		// Read the Sheet
		for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
			XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
			if (xssfSheet == null) {
				continue;
			}
			// Read the Row
			for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
				XSSFRow xssfRow = xssfSheet.getRow(rowNum);
				if (xssfRow != null) {
//					student = new Student();
//					XSSFCell no = xssfRow.getCell(0);
//					XSSFCell name = xssfRow.getCell(1);
//					XSSFCell age = xssfRow.getCell(2);
//					XSSFCell score = xssfRow.getCell(3);
//					student.setNo(getValue(no));
//					student.setName(getValue(name));
//					student.setAge(getValue(age));
//					student.setScore(Float.valueOf(getValue(score)));
//					list.add(student);
				}
			}
		}
		return list;
	}

	/**
	 * Read the Excel 2003-2007
	 * 
	 * @param path
	 *            the path of the Excel
	 * @return
	 * @throws IOException
	 */
	public static List<?> readXls(String path) throws IOException {
		System.out.println(PROCESSING + path);
		InputStream is = new FileInputStream(path);
		HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
//		Student student = null;
		List list = new ArrayList();
		// Read the Sheet
		for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
			HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
			if (hssfSheet == null) {
				continue;
			}
			// Read the Row
			for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
				HSSFRow hssfRow = hssfSheet.getRow(rowNum);
				if (hssfRow != null) {
//					student = new Student();
//					HSSFCell no = hssfRow.getCell(0);
//					HSSFCell name = hssfRow.getCell(1);
//					HSSFCell age = hssfRow.getCell(2);
//					HSSFCell score = hssfRow.getCell(3);
//					student.setNo(getValue(no));
//					student.setName(getValue(name));
//					student.setAge(getValue(age));
//					student.setScore(Float.valueOf(getValue(score)));
//					list.add(student);
				}
			}
		}
		return list;
	}

	@SuppressWarnings("static-access")
	private String getValue(XSSFCell xssfRow) {
		if (xssfRow.getCellType() == xssfRow.CELL_TYPE_BOOLEAN) {
			return String.valueOf(xssfRow.getBooleanCellValue());
		} else if (xssfRow.getCellType() == xssfRow.CELL_TYPE_NUMERIC) {
			return String.valueOf(xssfRow.getNumericCellValue());
		} else {
			return String.valueOf(xssfRow.getStringCellValue());
		}
	}

	@SuppressWarnings("static-access")
	private String getValue(HSSFCell hssfCell) {
		if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
			return String.valueOf(hssfCell.getBooleanCellValue());
		} else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
			return String.valueOf(hssfCell.getNumericCellValue());
		} else {
			return String.valueOf(hssfCell.getStringCellValue());
		}
	}
}